﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using WebBanThuoc.DTO;
using System.Data.SqlTypes;

namespace WebBanThuoc.DAO
{
    public class DonHang_DAO
    {

        private SqlConnection connection;
        //public bool hasData { set; get; }
        public DonHang_DAO(SqlConnection connection)
        {
            this.connection = connection;
        }

        private DonHang getBy(string whereCom, string orderCom)
        {
            DonHang data = null;
            string comStr = "Select * from [DonHang]";
            if (whereCom.Trim() != "")
                comStr += " where " + whereCom;
            if (orderCom.Trim() != "")
            {
                comStr += " order by " + orderCom;
            }

            try
            {
                IDataProvider dap = new DataProvider(connection);
                dap.ExecuteReader_Command(System.Data.CommandType.Text, comStr);
                //this.hasData = dap.HasData;
                if (dap.HasData)
                {
                    data = new DonHang();
                    dap.DaReader.Read();

                    data.MaDonHang = dap.DaReader["MaDonHang"].ToString();
                    data.IDKhachHang = Convert.ToInt32(dap.DaReader["IDKhachHang"]);
                    data.MaTinhTrang = dap.DaReader["MaTinhTrang"].ToString();
                    data.NgayDat = Convert.ToDateTime(dap.DaReader["NgayDat"]);
                    data.TongTien = Convert.ToDouble(dap.DaReader["TongTien"]);
                    data.TongKhuyenMai = Convert.ToDouble(dap.DaReader["TongKhuyenMai"]);
                    data.TenNguoiNhan = dap.DaReader["TenNguoiNhan"].ToString();
                    data.DiaChiNhan = dap.DaReader["DiaChiNhan"].ToString();
                    data.SoDienThoai = dap.DaReader["SoDienThoai"].ToString();
                    data.Email = dap.DaReader["Email"].ToString();
                    data.NgayCapNhat = Convert.ToDateTime(dap.DaReader["NgayCapNhat"]);
                    data.NgayGiao = dap.DaReader["NgayGiao"] != DBNull.Value ? Convert.ToDateTime(dap.DaReader["NgayGiao"]) : DateTime.MinValue;
                    data.IsStatus = Convert.ToInt32(dap.DaReader["IsStatus"]);
                    data.GhiChu = dap.DaReader["GhiChu"].ToString();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return data;
        }

        public DonHang getByKey(string MaDonHang)
        {
            return getBy(" [MaDonHang]='" + MaDonHang + "'", " [MaDonHang] asc");
        }

        private int delete(string whereCom)
        {
            string comUpdate = "DELETE FROM [DonHang] ";
            if (whereCom.Trim() != "")
                comUpdate += " where " + whereCom;
            try
            {
                IDataProvider dap = new DataProvider(this.connection);
                return dap.ExecuteNonQuery_Command(System.Data.CommandType.Text, comUpdate);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int deleteByKey(string MaDonHang)
        {
            return delete(" [MaDonHang]='" + MaDonHang + "'");
        }

        private int update(DonHang obj, string whereCom)
        {
            string comQuery = "UPDATE [DonHang] SET [MaDonHang]=@MaDonHang, [IDKhachHang]=@IDKhachHang, [MaTinhTrang]=@MaTinhTrang"
                + ", [NgayDat]=@NgayDat, [TongTien]=@TongTien, [TongKhuyenMai]=@TongKhuyenMai, [TenNguoiNhan]=@TenNguoiNhan"
                + ", [DiaChiNhan]=@DiaChiNhan, [SoDienThoai]=@SoDienThoai, [Email]=@Email, [NgayCapNhat]=@NgayCapNhat, [GhiChu]=@GhiChu";
            if (obj.NgayGiao == DateTime.MinValue)
            {
                comQuery += ", [NgayGiao]=NULL ";
            }
            else
            {
                comQuery += ", [NgayGiao]=@NgayGiao ";
            }
            if (whereCom.Trim() != "")
                comQuery += " where " + whereCom;
            try
            {
                IDataProvider dap = new DataProvider(connection);
                dap.AddParameters("@MaDonHang", obj.MaDonHang, System.Data.SqlDbType.NVarChar);
                dap.AddParameters("@IDKhachHang", obj.IDKhachHang, System.Data.SqlDbType.Int);
                dap.AddParameters("@MaTinhTrang", obj.MaTinhTrang, System.Data.SqlDbType.NChar);
                dap.AddParameters("@NgayDat", obj.NgayDat, System.Data.SqlDbType.SmallDateTime);
                dap.AddParameters("@TongTien", obj.TongTien, System.Data.SqlDbType.Decimal);
                dap.AddParameters("@TongKhuyenMai", obj.TongKhuyenMai, System.Data.SqlDbType.Decimal);
                dap.AddParameters("@TenNguoiNhan", obj.TenNguoiNhan, System.Data.SqlDbType.NVarChar);
                dap.AddParameters("@DiaChiNhan", obj.DiaChiNhan, System.Data.SqlDbType.NVarChar);
                dap.AddParameters("@SoDienThoai", obj.SoDienThoai, System.Data.SqlDbType.NVarChar);
                dap.AddParameters("@Email", obj.Email, System.Data.SqlDbType.NVarChar);
                dap.AddParameters("@NgayCapNhat", DateTime.Now, System.Data.SqlDbType.SmallDateTime);
                //dap.AddParameters("@NgayGiao", obj.NgayGiao != DateTime.MinValue ? obj.NgayGiao : SqlDateTime.Null, System.Data.SqlDbType.SmallDateTime);
                dap.AddParameters("@GhiChu", obj.GhiChu, System.Data.SqlDbType.NText);
                if (obj.NgayGiao != DateTime.MinValue)
                {
                    dap.AddParameters("@NgayGiao", obj.NgayGiao, System.Data.SqlDbType.SmallDateTime);
                }

                return dap.ExecuteNonQuery_Command(System.Data.CommandType.Text, comQuery);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public int updateByKey(DonHang donhang, string MaDonHang)
        {
            return update(donhang, " [MaDonHang]='" + MaDonHang + "' ");
        }

        public string createMaDonHang()
        {
            string result = "";
            try
            {
                IDataProvider dap = new DataProvider(connection);
                dap.ExecuteReader_Command(System.Data.CommandType.Text, "SELECT [dbo].[fnc_CreateNew_MaDonHang] () as col");
                //this.hasData = dap.HasData;
                if (dap.HasData)
                {
                    dap.DaReader.Read();
                    result = dap.DaReader["col"].ToString().Trim();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        public int add(DonHang obj)
        {
            string temp = ", NULL";
            if (obj.NgayGiao != DateTime.MinValue)
            {
                temp = ", @NgayGiao ";
            }

            string comQuery = "INSERT INTO [DonHang] ([MaDonHang],[IDKhachHang],[MaTinhTrang]"
                + ",[NgayDat],[TongTien],[TongKhuyenMai],[TenNguoiNhan],[DiaChiNhan]"
                + ",[SoDienThoai],[Email],[NgayCapNhat],[GhiChu], [NgayGiao])"
                + " VALUES (@MaDonHang,@IDKhachHang,@MaTinhTrang,@NgayDat,@TongTien,@TongKhuyenMai"
                + ",@TenNguoiNhan,@DiaChiNhan,@SoDienThoai,@Email,@NgayCapNhat,@GhiChu" + temp + ")";

            try
            {
                IDataProvider dap = new DataProvider(connection);
                if (obj.MaDonHang == null || obj.MaDonHang == "")
                {
                    obj.MaDonHang = createMaDonHang();
                }
                dap.AddParameters("@MaDonHang", obj.MaDonHang, System.Data.SqlDbType.NVarChar);
                dap.AddParameters("@IDKhachHang", obj.IDKhachHang, System.Data.SqlDbType.Int);
                dap.AddParameters("@MaTinhTrang", obj.MaTinhTrang, System.Data.SqlDbType.NChar);
                dap.AddParameters("@NgayDat", obj.NgayDat, System.Data.SqlDbType.SmallDateTime);
                dap.AddParameters("@TongTien", obj.TongTien, System.Data.SqlDbType.Decimal);
                dap.AddParameters("@TongKhuyenMai", obj.TongKhuyenMai, System.Data.SqlDbType.Decimal);
                dap.AddParameters("@TenNguoiNhan", obj.TenNguoiNhan, System.Data.SqlDbType.NVarChar);
                dap.AddParameters("@DiaChiNhan", obj.DiaChiNhan, System.Data.SqlDbType.NVarChar);
                dap.AddParameters("@SoDienThoai", obj.SoDienThoai, System.Data.SqlDbType.NVarChar);
                dap.AddParameters("@Email", obj.Email, System.Data.SqlDbType.NVarChar);
                dap.AddParameters("@NgayCapNhat", obj.NgayCapNhat, System.Data.SqlDbType.SmallDateTime);
                dap.AddParameters("@GhiChu", obj.GhiChu, System.Data.SqlDbType.NText);
                if (obj.NgayGiao != DateTime.MinValue)
                {
                    dap.AddParameters("@NgayGiao", obj.NgayGiao, System.Data.SqlDbType.SmallDateTime);
                }
                return dap.ExecuteNonQuery_Command(System.Data.CommandType.Text, comQuery);
            }
            catch (Exception ex)
            {
                throw ex;
            }


        }

        private List<DonHang> getList_View_DonHang_KhachHangByHoTen(string whereCom, string orderCom, List<SqlParameter> dataPrama)
        {
            List<DonHang> data = new List<DonHang>();
            string comStr = "Select * from [View_DonHang_KhachHangByHoTen]";
            if (whereCom.Trim() != "")
                comStr += " where " + whereCom;
            if (orderCom.Trim() != "")
            {
                comStr += " order by " + orderCom;
            }

            try
            {
                IDataProvider dap = new DataProvider(connection);
                dap.AddParameters(dataPrama);
                dap.ExecuteReader_Command(System.Data.CommandType.Text, comStr);
                //this.hasData = dap.HasData;
                if (dap.HasData)
                {
                    while (dap.DaReader.Read())
                    {
                        DonHang obj = new DonHang();

                        obj.MaDonHang = dap.DaReader["MaDonHang"].ToString();
                        obj.IDKhachHang = Convert.ToInt32(dap.DaReader["IDKhachHang"]);
                        obj.MaTinhTrang = dap.DaReader["MaTinhTrang"].ToString();
                        obj.NgayDat = Convert.ToDateTime(dap.DaReader["NgayDat"]);
                        obj.TongTien = Convert.ToDouble(dap.DaReader["TongTien"]);
                        obj.TongKhuyenMai = Convert.ToDouble(dap.DaReader["TongKhuyenMai"]);
                        obj.TenNguoiNhan = dap.DaReader["TenNguoiNhan"].ToString();
                        obj.DiaChiNhan = dap.DaReader["DiaChiNhan"].ToString();
                        obj.SoDienThoai = dap.DaReader["SoDienThoai"].ToString();
                        obj.Email = dap.DaReader["Email"].ToString();
                        obj.NgayCapNhat = Convert.ToDateTime(dap.DaReader["NgayCapNhat"]);
                        obj.NgayGiao = dap.DaReader["NgayGiao"] != DBNull.Value ? Convert.ToDateTime(dap.DaReader["NgayGiao"]) : DateTime.MinValue;
                        obj.IsStatus = Convert.ToInt32(dap.DaReader["IsStatus"]);
                        obj.GhiChu = dap.DaReader["GhiChu"].ToString();
                        data.Add(obj);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return data;
        }

        public List<DonHang> getList_View_DonHang_KhachHangByHoTenBy(string HoTenKhachHang, string MaTinhTrang, DateTime NgayDatBegin, DateTime NgayDatEnd)
        {
            string tempS = "";
            List<SqlParameter> dataPrama = new List<SqlParameter>();
            if (MaTinhTrang.Trim() != "")
            {
                tempS = " [MaTinhTrang]='" + MaTinhTrang.Trim() + "' ";
            }
            if (NgayDatBegin != DateTime.MinValue && NgayDatEnd != DateTime.MinValue)
            {
                tempS += (tempS != "" ? " and " : "") + " [NgayDat] between @NgayDatBegin and @NgayDatEnd ";
                SqlParameter p1 = new SqlParameter("@NgayDatBegin", System.Data.SqlDbType.SmallDateTime);
                p1.Value = NgayDatBegin;
                SqlParameter p2 = new SqlParameter("@NgayDatBegin", System.Data.SqlDbType.SmallDateTime) { };
                p1.Value = NgayDatBegin;
                dataPrama = new List<SqlParameter>(){
                    new SqlParameter("@NgayDatBegin", System.Data.SqlDbType.SmallDateTime){
                        Value = NgayDatBegin
                    },
                    new SqlParameter("@NgayDatEnd", System.Data.SqlDbType.SmallDateTime){
                        Value = NgayDatEnd
                    }
                };
            }

            tempS += (tempS != "" ? " and " : "") + " [HoTen] like N'%" + HoTenKhachHang + "%' ";

            return getList_View_DonHang_KhachHangByHoTen(tempS, " [NgayDat] asc", dataPrama);
        }
    }
}